import pymysql
import pymysql.cursors
from contextlib import contextmanager
# 尝试导入pymysqlpool，如果不可用则使用简单连接
use_pool = False
try:
    from pymysqlpool import ConnectionPool
    use_pool = True
except ImportError:
    print("警告: pymysqlpool未安装，将使用简单连接模式")

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',  # 数据库主机地址
    'user': 'root',       # 数据库用户名
    'password': '123456',  # 数据库密码
    'db': 'chinese_dictionary',  # 数据库名
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# 连接池配置
POOL_CONFIG = {
    'pool_name': 'mysql_pool',
    'pool_size': 10,  # 连接池大小
    'max_overflow': 5,  # 最大溢出连接数
    'pool_recycle': 3600,  # 连接回收时间（秒）
    **DB_CONFIG
}

# 创建连接池或使用简单连接
connection_pool = None

def init_db_pool():
    """初始化数据库连接池"""
    global connection_pool
    if use_pool:
        connection_pool = ConnectionPool(**POOL_CONFIG)
        print("数据库连接池初始化完成")
    else:
        print("使用简单连接模式，不创建连接池")

# 初始化连接池
init_db_pool()

@contextmanager
def get_db_connection():
    """数据库连接上下文管理器"""
    connection = None
    try:
        if use_pool and connection_pool is not None:
            # 使用连接池
            connection = connection_pool.get_conn()
            yield connection
        else:
            # 使用简单连接
            connection = pymysql.connect(**DB_CONFIG)
            yield connection
    except Exception as e:
        print(f"数据库连接错误: {e}")
        raise
    finally:
        if connection:
            connection.close()

def execute_query(query, params=None, fetch_one=False):
    """执行查询并返回结果"""
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(query, params or ())
            if fetch_one:
                return cursor.fetchone()
            else:
                return cursor.fetchall()

def execute_pagination_query(query, params=None, page=1, page_size=10):
    """执行分页查询
    
    Args:
        query: 查询SQL语句
        params: 查询参数
        page: 页码，从1开始
        page_size: 每页数量
        
    Returns:
        dict: 包含分页数据的字典
    """
    # 计算偏移量
    offset = (page - 1) * page_size
    
    # 执行查询获取数据
    paginated_query = f"{query} LIMIT %s OFFSET %s"
    paginated_params = (params or []) + [page_size, offset]
    
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            # 获取分页数据
            cursor.execute(paginated_query, paginated_params)
            data = cursor.fetchall()
            
            # 获取总数
            count_query = f"SELECT COUNT(*) as total FROM ({query}) as temp"
            cursor.execute(count_query, params or ())
            total = cursor.fetchone()['total']
    
    # 计算总页数
    total_pages = (total + page_size - 1) // page_size
    
    return {
        'data': data,
        'total': total,
        'page': page,
        'page_size': page_size,
        'total_pages': total_pages
    }

def execute_update(query, params=None):
    """执行更新操作（插入、更新、删除）"""
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(query, params or ())
            conn.commit()
            return affected_rows